/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package dao;

import java.util.ArrayList;

/**
 *
 * @author Snowflake
 */
public class ExecQuery {

    public static String standardQuery(String query) {
        query = trimMoreSpace(query);
        query = trimSpaceAroundComma(query);
        query = standardInsideOfParentheses(query);
        query = standardOutsideOfParentheses(query);
        return query;
    }

    public static String trimCharsFirstEnd(String str) {
        if (str.equals("") || str == null) {
            return str;
        } else {
            StringBuilder sb = new StringBuilder(str);
            if (sb.length() >= 2) {
                sb.deleteCharAt(0);
                sb.deleteCharAt(sb.length() - 1);
                str = sb.toString();
            }
        }
        return str;
    }
    /*
     * Hàm này cắt bỏ các khoang trắng dư thừa
     */

    public static String trimMoreSpace(String query) {
        query = query.trim();
        while (query.indexOf("  ") >= 0) //tim trong chuoi vi tri co 2 khoang trong tro len
        {
            query = query.replace("  ", " ");
        }
        return query;
    }

    /*
     * Hàm loại bỏ khoang trang truoc sau dau phay
     */
    public static String trimSpaceAroundComma(String query) {
        while (query.indexOf(" ,") >= 0) {
            query = query.replace(" ,", ",");
        }
        while (query.indexOf(", ") >= 0) {
            query = query.replace(", ", ",");
        }
        return query;
    }

    public static String standardInsideOfParentheses(String query) {
        //loại bỏ tất cả các khoảng trống trong (..), xử lý trong dấu ngoặc đơn
        while (query.indexOf("( ") >= 0) {
            query = query.replace("( ", "(");
        }
        while (query.indexOf(" )") >= 0) {
            query = query.replace(" )", ")");
        }
        ArrayList<String> temp = new ArrayList<String>();
        ArrayList<String> kq = new ArrayList<String>();
        StringBuilder sb = new StringBuilder(query);
        while (sb.indexOf(")") >= 0 && sb.indexOf("(") >= 0) {
            int index = sb.indexOf("(");
            temp.add(sb.substring(index, sb.indexOf(")") + 1));
            sb = sb.delete(index, sb.indexOf(")") + 1);
        }
        for (int i = 0; i < temp.size(); i++) {
            String k = temp.get(i);
            while (k.indexOf(" ") >= 0) {
                k = k.replace(" ", "");
            }
            while (k.indexOf(")") >= 0) {
                k = k.replace(")", "");
            }
            while (k.indexOf("(") >= 0) {
                k = k.replace("(", "");
            }
            kq.add(k);
        }
        for (int i = 0; i < kq.size(); i++) {
            query = query.replaceAll(temp.get(i), kq.get(i));
        }
        return query;
    }

    public static String standardFirstOfParentheses(String query) {
        if (query == null || query.equals("")) {
            return query;
        }
        StringBuilder sb = new StringBuilder(query);
        try {
            int index = 0;
            do {
                if (sb.charAt(index) == '(') {
                    if (index > 0) {
                        char k = sb.charAt(index - 1);
                        if (!Character.toString(k).equals(" ") && !Character.toString(k).equals("(")) {
                            sb.insert(index, " ");
                            index++;
                        }
                    }
                }
                index++;
            } while (index < sb.length());

        } catch (Exception ex) {
            return "";
        }
        return sb.toString();
    }

    public static String standardFollowOfParentheses(String query) {
        if (query == null || query.equals("")) {
            return query;
        }

        StringBuilder sb = new StringBuilder(query);
        try {
            int index = 0;
            do {
                if (sb.charAt(index) == ')') {
                    if (index >= 0 && index < sb.length() - 1) {
                        char k = sb.charAt(index + 1);
                        if (!Character.toString(k).equals(" ") && !Character.toString(k).equals(")")) {
                            sb.insert(index + 1, " ");
                            index = index + 2;
                        }
                    }
                }
                index++;
            } while (index < sb.length());

        } catch (Exception ex) {
            return "";
        }
        return sb.toString();
    }

    public static String standardOutsideOfParentheses(String query) {
        if (query == null || query.equals("")) {
            return query;
        }
        StringBuilder sb = new StringBuilder(query);
        try {
            int index = 0;
            do {
                if (sb.charAt(index) == '(') {
                    if (index > 0) {
                        char k = sb.charAt(index - 1);
                        if (!Character.toString(k).equals(" ") && !Character.toString(k).equals("(")) {
                            sb.insert(index, " ");
                            index++;
                        }
                    }
                }
                if (sb.charAt(index) == ')') {
                    if (index >= 0 && index < sb.length() - 1) {
                        char k = sb.charAt(index + 1);
                        if (!Character.toString(k).equals(" ") && !Character.toString(k).equals(")")) {
                            sb.insert(index + 1, " ");
                            index = index + 2;
                        }
                    }
                }
                index++;
            } while (index < sb.length());

        } catch (Exception ex) {
            return "";
        }
        return sb.toString();
    }
    /*
     * Ham cat 1 chuôi theo dấu phẩy với điều kiện dấu phẩy không nắm trong nháy đơn
     */

    public static ArrayList<String> splitComma(String values) {
        ArrayList<String> list = new ArrayList<String>();
        if (values == null || values.equals("")) {
            return list;
        }
        try {
            int length = values.length();
            StringBuilder sb = new StringBuilder("");

            boolean closedApostrophe = true;//coi nháy đơn ket thuc chua hay không
            int apostropheCount = 0; // dem so nhay don
            for (int i = 0; i < length; i++) {
                if (i == length - 1) {
                    if (values.charAt(i) == ',') {
                        list.add(sb.toString());
                        list.add("");
                    } else {
                        sb.append(values.charAt(i));
                        list.add(sb.toString());
                    }
                } else if (values.charAt(i) == '\'') {
                    apostropheCount++;
                    if (apostropheCount % 2 == 1) {//mo dau nhay don
                        closedApostrophe = false;
                    } else {
                        closedApostrophe = true;
                    }
                    sb.append(values.charAt(i));
                } else if (values.charAt(i) == ',') {
                    if (closedApostrophe == true) {
                        list.add(sb.toString());
                        sb = new StringBuilder("");
                    } else {
                        sb.append(values.charAt(i));
                    }
                } else {
                    sb.append(values.charAt(i));
                }
            }
        } catch (Exception ex) {
            return list = new ArrayList<String>();
        }
        return list;
    }
    /*
     * Ham cat 1 chuôi theo dấu bằng với điều kiện dấu bằng không nắm trong nháy đơn
     */

    public static ArrayList<String> splitEqual(String values) {
        ArrayList<String> list = new ArrayList<String>();
        if (values == null || values.equals("")) {
            return list;
        }
        try {
            int length = values.length();
            StringBuilder sb = new StringBuilder("");

            boolean closedApostrophe = true;//coi nháy đơn ket thuc chua hay không
            int apostropheCount = 0; // dem so nhay don
            for (int i = 0; i < length; i++) {
                if (i == length - 1) {
                    if (values.charAt(i) == '=') {
                        list.add(sb.toString());
                        list.add("");
                    } else {
                        sb.append(values.charAt(i));
                        list.add(sb.toString());
                    }
                } else if (values.charAt(i) == '\'') {
                    apostropheCount++;
                    if (apostropheCount % 2 == 1) {//mo dau nhay don
                        closedApostrophe = false;
                    } else {
                        closedApostrophe = true;
                    }
                    sb.append(values.charAt(i));
                } else if (values.charAt(i) == '=') {
                    if (closedApostrophe == true) {
                        list.add(sb.toString());
                        sb = new StringBuilder("");
                    } else {
                        sb.append(values.charAt(i));
                    }
                } else {
                    sb.append(values.charAt(i));
                }
            }
        } catch (Exception ex) {
            return list = new ArrayList<String>();
        }
        return list;
    }
    /*
     * Hàm này cắt chuỗi theo or
     */

    public static ArrayList<String> splitOr(String where) {
        ArrayList<String> list = new ArrayList<String>();
        if (where == null || where.equals("")) {
            return list;
        }
        try {
            int length = where.length();
            StringBuilder sbValues = new StringBuilder(where);
            StringBuilder sb = new StringBuilder("");
            boolean closedApostrophe = true;//coi nháy đơn ket thuc chua hay không
            int apostropheCount = 0; // dem so nhay don
            if (length < 5) {
                if (where.equalsIgnoreCase(" or ")) {
                    list.add("");
                    list.add("");
                } else {
                    list.add(where);
                }
            } else {
                for (int i = 0; i < length; i++) {
                    if (i == length - 4) {
                        if (sbValues.substring(i, length).equalsIgnoreCase(" or ")) {
                            list.add(sb.toString());
                            list.add("");
                            i = i + 3;
                        } else {
                            sb.append(where.subSequence(i, length));
                            list.add(sb.toString());
                            break;
                        }
                    } else if (where.charAt(i) == '\'') {
                        apostropheCount++;
                        if (apostropheCount % 2 == 1) {//mo dau nhay don
                            closedApostrophe = false;
                            sb.append(where.charAt(i));
                        } else {
                            closedApostrophe = true;
                            //xem co truong hop or dinh và ' không, nhu sau : tenlop= '...'or malop=2
                            if (sbValues.substring(i + 1, i + 4).equalsIgnoreCase("or ")) {
                                if (closedApostrophe == true) {
                                    sb.append(where.charAt(i));
                                    list.add(sb.toString());
                                    sb = new StringBuilder("");
                                    i = i + 3;
                                    if (i > length - 4) {
                                        sb.append(sbValues.substring(i, length));
                                        list.add(sb.toString());
                                        break;
                                    }
                                } else {
                                    sb.append(where.charAt(i));
                                }
                            }
                        }

                    } else if (where.charAt(i) == ' ') {
                        if (sbValues.substring(i, i + 4).equalsIgnoreCase(" or ")) {
                            if (closedApostrophe == true) {
                                list.add(sb.toString());
                                sb = new StringBuilder("");
                                i = i + 3;
                                if (i > length - 4) {
                                    sb.append(sbValues.substring(i, length));
                                    list.add(sb.toString());
                                    break;
                                }
                            } else {
                                sb.append(where.charAt(i));
                            }
                        } else {
                            sb.append(where.charAt(i));
                        }
                    } else {
                        sb.append(where.charAt(i));
                    }
                }
            }
        } catch (Exception ex) {
            return list = new ArrayList<String>();
        }
        return list;
    }
    /*
     * Hàm này split theo and, ko bao gồm AND trong dấu nháy ' '
     */

    public static ArrayList<String> splitAnd(String where) {
        ArrayList<String> list = new ArrayList<String>();
        if (where == null || where.equals("")) {
            return list;
        }
        try {
            int length = where.length();
            StringBuilder sbValues = new StringBuilder(where);
            StringBuilder sb = new StringBuilder("");
            boolean closedApostrophe = true;//coi nháy đơn ket thuc chua hay không
            int apostropheCount = 0; // dem so nhay don
            if (length < 6) {
                if (where.equalsIgnoreCase(" and ")) {
                    list.add("");
                    list.add("");
                } else {
                    list.add(where);
                }
            } else {
                for (int i = 0; i < length; i++) {
                    if (i == length - 5) {
                        if (sbValues.substring(i, i + 5).equalsIgnoreCase(" and ")) {
                            list.add(sb.toString());
                            list.add("");
                            i = i + 4;
                        } else {
                            sb.append(where.subSequence(i, length));
                            list.add(sb.toString());
                            break;
                        }
                    } else if (where.charAt(i) == '\'') {
                        apostropheCount++;
                        if (apostropheCount % 2 == 1) {//mo dau nhay don
                            closedApostrophe = false;
                            sb.append(where.charAt(i));
                        } else {
                            closedApostrophe = true;
                            //xet xem co truong hop AND dính vào dấu ' không
                            if (sbValues.substring(i + 1, i + 5).equalsIgnoreCase("and ")) {
                                sb.append(where.charAt(i));
                                list.add(sb.toString());
                                sb = new StringBuilder("");
                                i = i + 4;
                                if (i > length - 5) {
                                    sb.append(sbValues.substring(i, length));
                                    list.add(sb.toString());
                                    break;
                                }
                            } else {
                                sb.append(where.charAt(i));
                            }
                        }
                    } else if (where.charAt(i) == ' ') {
                        if (sbValues.substring(i, i + 5).equalsIgnoreCase(" and ")) {
                            if (closedApostrophe == true) {
                                list.add(sb.toString());
                                sb = new StringBuilder("");
                                i = i + 4;
                                if (i > length - 5) {
                                    sb.append(sbValues.substring(i, length));
                                    list.add(sb.toString());
                                    break;
                                }
                            } else {
                                sb.append(where.charAt(i));
                            }
                        } else {
                            sb.append(where.charAt(i));
                        }
                    } else {
                        sb.append(where.charAt(i));
                    }
                }
            }
        } catch (Exception ex) {
            return list = new ArrayList<String>();
        }
        return list;
    }
 /*
     * Hàm này split theo where, khong bao gom where trong dau ''
     */

    public static ArrayList<String> splitWhere(String where) {
        ArrayList<String> list = new ArrayList<String>();
        if (where == null || where.equals("")) {
            return list;
        }
        try {
            int length = where.length();
            StringBuilder sbValues = new StringBuilder(where);
            StringBuilder sb = new StringBuilder("");
            boolean closedApostrophe = true;//coi nháy đơn ket thuc chua hay không
            int apostropheCount = 0; // dem so nhay don
            if (length < 8) {
                if (where.equalsIgnoreCase(" where ")) {
                    list.add("");
                    list.add("");
                } else {
                    list.add(where);
                }
            } else {
                for (int i = 0; i < length; i++) {
                    if (i == length - 7) {
                        if (sbValues.substring(i, length).equalsIgnoreCase(" where ")) {
                            list.add(sb.toString());
                            list.add("");
                            i = i + 6;
                        } else {
                            sb.append(where.subSequence(i, length));
                            list.add(sb.toString());
                            break;
                        }
                    } else if (where.charAt(i) == '\'') {
                        apostropheCount++;
                        if (apostropheCount % 2 == 1) {//mo dau nhay don
                            closedApostrophe = false;
                            sb.append(where.charAt(i));
                        } else {
                            closedApostrophe = true;
                            //xet xem co truong hop AND dính vào dấu ' không
                            if (sbValues.substring(i + 1, i + 7).equalsIgnoreCase("where ")) {
                                sb.append(where.charAt(i));
                                list.add(sb.toString());
                                sb = new StringBuilder("");
                                i = i + 6;
                                if (i > length - 7) {
                                    sb.append(sbValues.substring(i, length));
                                    list.add(sb.toString());
                                    break;
                                }
                            } else {
                                sb.append(where.charAt(i));
                            }
                        }
                    } else if (where.charAt(i) == ' ') {
                        if (sbValues.substring(i, i + 7).equalsIgnoreCase(" where ")) {
                            if (closedApostrophe == true) {
                                list.add(sb.toString());
                                sb = new StringBuilder("");
                                i = i + 6;
                                if (i > length - 7) {
                                    sb.append(sbValues.substring(i, length));
                                    list.add(sb.toString());
                                    break;
                                }
                            } else {
                                sb.append(where.charAt(i));
                            }
                        } else {
                            sb.append(where.charAt(i));
                        }
                    } else {
                        sb.append(where.charAt(i));
                    }
                }
            }
        } catch (Exception ex) {
            return list = new ArrayList<String>();
        }
        return list;
    }

    /*
     * Hàm nay chuan hoa AND OR ve kieu thuong
     */
    public static String standardAndOrKey(String where) {
        if (where == null || where.equals("")) {
            return where;
        }
        try {
            String[] andKeys = new String[]{" AND ", " ANd ", " AnD ", " And ", " aND ", " aNd ", " anD "};
            String[] orKeys = new String[]{" OR ", " Or ", " oR "};
            for (int i = 0; i < andKeys.length; i++) {
                where = where.replace(andKeys[i], " and ");
            }
            for (int i = 0; i < orKeys.length; i++) {
                where = where.replace(orKeys[i], " or ");
            }
        } catch (Exception ex) {
            return where;
        }
        return where;
    }
}
